﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Shohdi.DataHandler;
using System.Data;

namespace dbComponentGenerator
{
    public class dbComponentGenerater
    {

        /// <summary>
        /// this will generate the table adapter from the database
        /// </summary>
        /// <param name="tableName">the table name</param>
        /// <param name="Connection">the connection string</param>
        /// <param name="Provider">the database provider of the connection</param>
        /// <returns>the generated file in a string (write it to a file .cs)</returns>
        public static string GenerateTableAdapter (string tableName, string Connection, string Provider)
        {
            ClassDataHandler con = new ClassDataHandler(Connection, Provider);
            con.OpenConnection();

            DataTable dt = con.SelectSchemaInDataTable(" select * from dbo.[" + tableName + "] where 1 <> 1 ", CommandType.Text);

            if (dt == null)
                throw new Exception("لا يوجد جدول بهذا الاسم");

            if (dt.Columns.Count == 0)
                throw new Exception("لا يوجد اعمدة في هذا الجدول");

            dt.TableName = tableName;


            con.CloseConnection();


            if (dt.Columns.Count == 0)
                return "";



            return GenerateAdapter(dt);



        }









        /// <summary>
        /// this function will generate a table adapter from a datatable holding the schema
        /// </summary>
        /// <param name="dtNew">the datatable having the schema</param>
        /// <returns>the string having the generated code ( write it to a .cs file )</returns>
        private static string GenerateAdapter(DataTable dtNew)
        {
            
            
            StringBuilder sb = new StringBuilder("");
            string strTableName = dtNew.TableName.Replace(" ", "_");
            
            string strRet = @"



// This code is a generated code by a tool
// designed and coded by Shohdi Mohamed Elsheemy ( shohdi@gmail.com )            

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using Shohdi.DataHandler;
using System.Data;




namespace " + dbComponentGenerator.GlobalVars.strNameSpace + @"
{
    
    public partial class " + strTableName + @"DB
    {
         string _Connection = """";


        public string Connection
        {
            get { return _Connection; }
            set { _Connection = value; }
        }


       
         string _Provider = ""System.Data.SqlClient"";

        
        public string Provider
        {
            get { return _Provider; }
            set { _Provider = value; }
        }



        public " + strTableName + @"DB()
        {
        }

        public " + strTableName + @"DB(string __Connection,params string [] __Provider)
        {

            if (__Provider.Length > 0)
                _Provider = __Provider[0];

            _Connection = __Connection;
            
        }

" ;


            sb.Append(strRet);




            strRet = @"


        public List<" + strTableName + @"> Select(ClassDataHandler con, string strTop, string strWhere, params DbParameter[] whereParams)
        {
            if (whereParams == null)
                whereParams = new DbParameter[0];

            List<" + strTableName + @"> lstRet = new List<" + strTableName + @">();

            string strQuery = "" select "" + strTop  + "" * from dbo.["+ dtNew.TableName + @"]  "" ;

            if (strWhere != """")
            {
                strQuery = strQuery + ""  "" + strWhere;
            }

            DbDataReader dr = con.SelectDataReader(strQuery, CommandType.Text, whereParams);


            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    " + strTableName + @" obj = new " + strTableName + @"();
";



            sb.Append(strRet);

            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                string strDbColumnName = dtNew.Columns[i].ColumnName;


                sb.AppendLine(@" if (dr[""" + strDbColumnName + @"""] != DBNull.Value)
                    {
                        obj." + strColumnName + @" = (" + getTypeDeclarationFromColumn(dtNew.Columns[i]) + @")dr[""" + strDbColumnName + @"""];
                    }
                    else
                    {
                        obj." + strColumnName + @" = "+ getTypeInitialValue(dtNew.Columns[i]) +@";
                    } ");
            }



            strRet = @"


                    lstRet.Add(obj);
                   

                }
            }

            dr.Close();


            return lstRet;
        }


        public List<" + strTableName + @"> Select(string strTop, string strWhere, params DbParameter[] whereParams)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);

            con.OpenConnection();

            List<" + strTableName + @"> lstRet = Select(con, strTop, strWhere, whereParams);

            con.CloseConnection();

            return lstRet;
        }

        



        public void Insert(ClassDataHandler con , " + strTableName + @" obj)
        {
            
            ";

            sb.Append(strRet);


            for (int i = 0; i < dtNew.Columns.Count; i++)
            {

                if (!dtNew.Columns[i].AutoIncrement)
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    sb.AppendLine(@"

                    DbParameter prm"+ strColumnName + @" = con.createParameter();
                    prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                    prm" + strColumnName + @".DbType ="+ getDbType (dtNew.Columns[i])  + @";
                    if (obj." + strColumnName + @" == null)
                    {
                        prm" + strColumnName + @".Value = DBNull.Value;
                    }
                    else
                    {
                        prm" + strColumnName + @".Value = obj." + strColumnName + @";
                    }
                

                    ");

                }
            }


            strRet = @"

           



            string strQuery = "" insert into dbo.["+ dtNew.TableName +@"] (";
            
            sb.Append (strRet);
            string strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.Columns[i].AutoIncrement)
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    strValue = strValue + "[" + strDbColumnName + "],";
                    



                }
            }

            if (strValue.Length > 0)
                strValue = strValue.Substring(0,strValue.LastIndexOf(","));
            sb.Append(strValue);

            sb.Append(@") VALUES (");


            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.Columns[i].AutoIncrement)
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    strValue = strValue + "@" + strColumnName + ",";




                }
            }

            if (strValue.Length > 0)
                strValue = strValue.Substring(0, strValue.LastIndexOf(","));
            sb.Append(strValue);


            sb.AppendLine(@") ; select SCOPE_IDENTITY() "";");



            sb.Append(@" object scope = con.ExecuteScalar(strQuery, CommandType.Text, ");



            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.Columns[i].AutoIncrement)
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    strValue = strValue + "prm" + strColumnName + ",";




                }
            }

            if (strValue.Length > 0)
                strValue = strValue.Substring(0, strValue.LastIndexOf(","));
            sb.Append(strValue);

            sb.AppendLine(");");



            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.Columns[i].AutoIncrement)
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    sb.AppendLine(@"

                    try
                    {
                        obj."+strColumnName+@" = "+ getTypeDeclarationFromColumn(dtNew.Columns[i]) +@".Parse(scope.ToString());
                    }
                    catch
                    {
                        obj." + strColumnName + @" = "+ getTypeInitialValue(dtNew.Columns[i]) +@";
                    }


                    ");


                }
            }

            
            
            strRet = @"

           

        }



        public void Insert("+ strTableName +@" obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);
            con.OpenConnection();

            Insert(con,obj);


            con.CloseConnection();
        }




        public void Update(ClassDataHandler con, " + strTableName + @" obj)
        {
            
            ";


            sb.Append(strRet);

            for (int i = 0; i < dtNew.Columns.Count; i++)
            {

                

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    sb.AppendLine(@"

                    DbParameter prm" + strColumnName + @" = con.createParameter();
                    prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                    prm" + strColumnName + @".DbType =" + getDbType(dtNew.Columns[i]) + @";
                    if (obj." + strColumnName + @" == null)
                    {
                        prm" + strColumnName + @".Value = DBNull.Value;
                    }
                    else
                    {
                        prm" + strColumnName + @".Value = obj." + strColumnName + @";
                    }
                

                    ");

                
            }

            strRet = @"

           



            string strQuery = "" update dbo.[" + dtNew.TableName + @"] set ";
            
            sb.Append (strRet);

            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {
                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;

                    strValue = strValue + @"[" + strDbColumnName + @"]=@"+ strColumnName +@",";
                }
            }

            if (strValue.Length > 0)
                strValue = strValue.Substring(0, strValue.LastIndexOf(","));

            sb.Append(strValue);


            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {
                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;

                    strValue = strValue + @" [" + strDbColumnName + @"]=@" + strColumnName + @" and ";
                }
            }
            if (strValue.Length > 0)
            {
                strValue = " where " + strValue;
                strValue = strValue.Substring(0, strValue.LastIndexOf("and"));
            }
            sb.Append(strValue);


            strRet = @" "";

            con.ExecuteNonQuery(strQuery, CommandType.Text,";

            sb.Append(strRet);


            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {



                string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                string strDbColumnName = dtNew.Columns[i].ColumnName;

                strValue = strValue + @"prm" + strColumnName + ",";

            }
            if (strValue.Length > 0)
                strValue = strValue.Substring(0, strValue.LastIndexOf(","));

            sb.Append(strValue);


            
            strRet = @");

            

        }


        public void Update(" + strTableName +@" obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);
            con.OpenConnection();

            Update(con, obj);


            con.CloseConnection();
        }



        public void Delete(ClassDataHandler con, " + strTableName + @" obj)
        {

            ";

            sb.Append (strRet);


            for (int i = 0; i < dtNew.Columns.Count; i++)
            {


                if (dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    sb.AppendLine(@"

                    DbParameter prm" + strColumnName + @" = con.createParameter();
                    prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                    prm" + strColumnName + @".DbType =" + getDbType(dtNew.Columns[i]) + @";
                    if (obj." + strColumnName + @" == null)
                    {
                        prm" + strColumnName + @".Value = DBNull.Value;
                    }
                    else
                    {
                        prm" + strColumnName + @".Value = obj." + strColumnName + @";
                    }
                

                    ");
                }


            }


            strRet = @"

           



           



            string strQuery = "" delete from dbo.["+ dtNew.TableName +@"] ";

            sb.Append(strRet);

            strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {
                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;

                    strValue = strValue + @" [" + strDbColumnName + @"]=@" + strColumnName + @" and ";
                }
            }
            if (strValue.Length > 0)
            {
                strValue = " where " + strValue;
                strValue = strValue.Substring(0, strValue.LastIndexOf("and"));
            }
            sb.Append(strValue);

            sb.Append(@" ""; 
                        ");



            strValue = "con.ExecuteNonQuery(strQuery, CommandType.Text,";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {
                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;

                    strValue = strValue + @"prm" + strColumnName + @",";
                }
            }
            if (strValue.Length >0)
                strValue = strValue.Substring(0, strValue.LastIndexOf(","));

            sb.Append(strValue);

            sb.Append(@" ); ");

            
            strRet = @" 

             



        }


        public void Delete( " + strTableName + @" obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);

            con.OpenConnection();

            Delete(con, obj);

            con.CloseConnection();
        }



";


            sb.Append(strRet);



            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                string strDbColumnName = dtNew.Columns[i].ColumnName;

                sb.Append(@"

                    public List<" + strTableName + @"> get" + strTableName + @"By"+ strColumnName +@"(ClassDataHandler con,"+ getTypeDeclarationFromColumn (dtNew.Columns[i]) +@" "+ strColumnName +@")
                    {
                        List<" + strTableName + @"> lstRet = new List<" + strTableName + @">();


                        DbParameter prm" + strColumnName + @" = con.createParameter();
                        prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                        prm" + strColumnName + @".DbType = " + getDbType (dtNew.Columns[i]) + @";
                        if (" + strColumnName + @" == null)
                        {
                            return lstRet;
                        }
                        else
                        {
                            prm" + strColumnName + @".Value = " + strColumnName + @";
                        }


                        lstRet = Select(con, """" , "" where [" + strDbColumnName + @"]=@" + strColumnName + @" "", prm" + strColumnName + @");



                        return lstRet;

                    }


                    public List<" + strTableName + @"> get" + strTableName + @"By" + strColumnName + @"(" + getTypeDeclarationFromColumn(dtNew.Columns[i]) + @" " + strColumnName + @")
                    {
                        List<" + strTableName + @"> lstRet = new List<" + strTableName + @">();

                        ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);
                        con.OpenConnection();

                        lstRet = get" + strTableName + @"By" + strColumnName + @"(con, " + strColumnName + @");

                        con.CloseConnection();

                        return lstRet;

                    }



                ");


            }




            sb.Append(@"

            
public List<" + strTableName + @"> Search(ClassDataHandler con, " + strTableName + @" obj)
        {
            string strWhere = """";
            List<DbParameter> lstParameters = new List<DbParameter>();

            ");



            for (int i = 0; i < dtNew.Columns.Count; i++)
            {



                string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                string strDbColumnName = dtNew.Columns[i].ColumnName;


                sb.AppendLine(@"
                    if (obj." + strColumnName + @" != "+ getTypeInitialValue(dtNew.Columns[i]) +@")
                    {
                        DbParameter prm" + strColumnName + @" = con.createParameter();
                        prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                        prm" + strColumnName + @".DbType =" + getDbType(dtNew.Columns[i]) + @";
                        if (obj." + strColumnName + @" == null)
                        {
                            prm" + strColumnName + @".Value = DBNull.Value;
                        }
                        else
                        {
                            prm" + strColumnName + @".Value = obj." + strColumnName + @";
                        }
                        strWhere += "" [" + strDbColumnName + @"]=@" + strColumnName + @" AND "";
                        lstParameters.Add(prm" + strColumnName + @");
                     }



                    ");

                    


            }

            sb.Append(@"

            if (strWhere != """")
                        strWhere = "" where "" + strWhere.Substring(0, strWhere.LastIndexOf(""AND""));

                    ");

    
            




            sb.Append (@"

            

            List<" + strTableName + @"> lstRet = Select(con, """", strWhere, lstParameters.ToArray());

            return lstRet;
        }


        public List<" + strTableName + @"> Search(" + strTableName + @" obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);

            con.OpenConnection();

            List<" + strTableName + @"> lstRet = Search(con,obj);

            con.CloseConnection();

            return lstRet;
        }


                ");




            sb.Append(@"

            
public List<" + strTableName + @"> SearchOrLike(ClassDataHandler con, " + strTableName + @" obj)
        {
            string strWhere = """";
            List<DbParameter> lstParameters = new List<DbParameter>();

            ");



            for (int i = 0; i < dtNew.Columns.Count; i++)
            {



                string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                string strDbColumnName = dtNew.Columns[i].ColumnName;
                string comparison = "";

                if (dtNew.Columns[i].DataType == typeof(string))
                {
                    comparison = " LIKE ";
                }
                else
                {
                    comparison = "=";
                }

                sb.AppendLine(@"
                    if (obj." + strColumnName + @" != " + getTypeInitialValue(dtNew.Columns[i]) + @")
                    {
                        DbParameter prm" + strColumnName + @" = con.createParameter();
                        prm" + strColumnName + @".ParameterName = ""@" + strColumnName + @""";
                        prm" + strColumnName + @".DbType =" + getDbType(dtNew.Columns[i]) + @";
                        if (obj." + strColumnName + @" == null)
                        {
                            prm" + strColumnName + @".Value = DBNull.Value;
                        }
                        else
                        {
                            prm" + strColumnName + @".Value = obj." + strColumnName + @";
                        }
                        strWhere += "" [" + strDbColumnName + @"]" + comparison +  @"@" + strColumnName + @" OR "";
                        lstParameters.Add(prm" + strColumnName + @");
                     }



                    ");




            }

            sb.Append(@"

            if (strWhere != """")
                        strWhere = "" where "" + strWhere.Substring(0, strWhere.LastIndexOf(""OR""));

                    ");







            sb.Append(@"

            

            List<" + strTableName + @"> lstRet = Select(con, """", strWhere, lstParameters.ToArray());

            return lstRet;
        }


        public List<" + strTableName + @"> SearchOrLike(" + strTableName + @" obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);

            con.OpenConnection();

            List<" + strTableName + @"> lstRet = SearchOrLike(con,obj);

            con.CloseConnection();

            return lstRet;
        }


                ");




            strRet = @"
        


       
        



    }
}











            ";

            sb.Append(strRet);



            return sb.ToString();



        }




        /// <summary>
        /// this will generate the table adapter empty from the database
        /// </summary>
        /// <param name="tableName">the table name</param>
        /// <param name="Connection">the connection string</param>
        /// <param name="Provider">the database provider of the connection</param>
        /// <returns>the generated file in a string (write it to a file .cs)</returns>
        public static string GenerateTableAdapterEmpty(string tableName, string Connection, string Provider)
        {
            ClassDataHandler con = new ClassDataHandler(Connection, Provider);
            con.OpenConnection();

            DataTable dt = con.SelectSchemaInDataTable(" select * from dbo.[" + tableName + "] where 1 <> 1 ", CommandType.Text);

            if (dt == null)
                throw new Exception("لا يوجد جدول بهذا الاسم");

            if (dt.Columns.Count == 0)
                throw new Exception("لا يوجد اعمدة في هذا الجدول");

            dt.TableName = tableName;


            con.CloseConnection();


            if (dt.Columns.Count == 0)
                return "";



            return GenerateAdapterEmpty(dt);



        }




        /// <summary>
        /// this function will generate a table adapter Empty Class from a datatable holding the schema
        /// </summary>
        /// <param name="dtNew">the datatable having the schema</param>
        /// <returns>the string having the generated code ( write it to a .cs file )</returns>
        private static string GenerateAdapterEmpty(DataTable dtNew)
        {


            StringBuilder sb = new StringBuilder("");
            string strTableName = dtNew.TableName.Replace(" ", "_");

            string strRet = @"



           

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using Shohdi.DataHandler;
using System.Data;




namespace " + dbComponentGenerator.GlobalVars.strNameSpace + @"
{
    
    public partial class " + strTableName + @"DB
    {
          //Write any additional methods or properties here 
          //Don't modify any genrated classes
    }

}

                        ";


            return strRet;
        }






        /// <summary>
        /// will generate table class that have properties of all table fields
        /// </summary>
        /// <param name="tableName">the table name</param>
        /// <param name="Connection">the connection to the database</param>
        /// <param name="Provider">the provider used to access the database</param>
        public static string GenerateTableClass(string tableName, string Connection, string Provider)
        {
            ClassDataHandler con = new ClassDataHandler(Connection, Provider);
            con.OpenConnection();

            DataTable dt = con.SelectSchemaInDataTable(" select * from dbo.[" + tableName + "] where 1 <> 1 ", CommandType.Text);

            if (dt == null)
                throw new Exception("لا يوجد جدول بهذا الاسم");

            if (dt.Columns.Count == 0)
                throw new Exception("لا يوجد اعمدة في هذا الجدول");

            dt.TableName = tableName;


            con.CloseConnection();


            if (dt.Columns.Count == 0)
                return "";



           return GenerateClass(dt);



        }

        /// <summary>
        /// Generate table class from a datatable
        /// </summary>
        /// <param name="dtNew">the datatable to generate its class</param>
        private static string GenerateClass(DataTable dtNew)
        {
            string clType = null;
            string strName = "";
            StringBuilder sb = new StringBuilder("");
            string strTableName = dtNew.TableName.Replace(" ", "_");


            bool bIncrement = false;

            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.Columns[i].AutoIncrement)
                {
                    bIncrement = true;
                    break;
                }
            }


            sb.AppendLine(@"

            
// This code is a generated code by a tool
// designed and coded by Shohdi Mohamed Elsheemy ( shohdi@gmail.com )

            ");
            
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Web;");

            sb.AppendLine("namespace " + dbComponentGenerator.GlobalVars.strNameSpace + @"");
            sb.AppendLine("{");



            sb.AppendLine("[Serializable]");
            sb.AppendLine("public partial class " + strTableName);
            sb.AppendLine("{");

            
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                   

                 clType = getTypeDeclarationFromColumn( dtNew.Columns[i]);
                 strName = dtNew.Columns[i].ColumnName.Replace(" ","_");

                string strInit = getTypeInitialValue(dtNew.Columns[i]);


                sb.AppendLine(clType + " " + "_" + strName + " " + "=" + strInit + ";");

                sb.AppendLine("public " + clType + " " + strName);

                sb.AppendLine("{");


                sb.AppendLine("get { return _" + strName + ";}");

                sb.AppendLine("set { _" + strName + " = value;}");



                sb.AppendLine("}");
            }




        //    public Users()
        //{
        //    //
        //    // TODO: Add constructor logic here
        //    //
        //}


            sb.AppendLine("public " + strTableName + "()");

            sb.AppendLine("{");
            sb.AppendLine("}");





            string strConstractorParams = "";


            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.Columns[i].AutoIncrement)
                {
                    clType = getTypeDeclarationFromColumn(dtNew.Columns[i]);
                    strName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    strName = "__" + strName;

                    strConstractorParams += clType + " " + strName + ",";

                }

                
            }

            if (strConstractorParams.Trim() != "")
                strConstractorParams = strConstractorParams.Substring(0,strConstractorParams.LastIndexOf(","));


            sb.AppendLine("public " + strTableName + "(" + strConstractorParams + ")");

            sb.AppendLine("{");

            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (!dtNew.Columns[i].AutoIncrement)
                {
                    strName = dtNew.Columns[i].ColumnName.Replace(" ", "_");


                    sb.AppendLine("_" + strName + " = " + "__" + strName + ";");
                }
            }

            sb.AppendLine("}");




            if (bIncrement)
            {
                strConstractorParams = "";


                for (int i = 0; i < dtNew.Columns.Count; i++)
                {

                    clType = getTypeDeclarationFromColumn(dtNew.Columns[i]);
                    strName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    strName = "__" + strName;

                    strConstractorParams += clType + " " + strName + ",";




                }

                if (strConstractorParams.Trim() != "")
                    strConstractorParams = strConstractorParams.Substring(0, strConstractorParams.LastIndexOf(","));


                sb.AppendLine("public " + strTableName + "(" + strConstractorParams + ")");

                sb.AppendLine("{");

                for (int i = 0; i < dtNew.Columns.Count; i++)
                {

                    strName = dtNew.Columns[i].ColumnName.Replace(" ", "_");


                    sb.AppendLine("_" + strName + " = " + "__" + strName + ";");

                }

                sb.AppendLine("}");

            }




            string strValue = "";
            for (int i = 0; i < dtNew.Columns.Count; i++)
            {
                if (dtNew.PrimaryKey.Contains(dtNew.Columns[i]))
                {

                    string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                    string strDbColumnName = dtNew.Columns[i].ColumnName;


                    strValue = strValue + "newObj." + strColumnName + " == this." + strColumnName + " && ";




                }
            }

            if (strValue != "")
            {
                strValue = strValue.Substring(0, strValue.LastIndexOf("&&"));
            }
            else
            {
                for (int i = 0; i < dtNew.Columns.Count; i++)
                {
                    

                        string strColumnName = dtNew.Columns[i].ColumnName.Replace(" ", "_");
                        string strDbColumnName = dtNew.Columns[i].ColumnName;


                        strValue = strValue + "newObj." + strColumnName + " == this." + strColumnName + " && ";




                    
                }

                strValue = strValue.Substring(0, strValue.LastIndexOf("&&"));
            }
            



            sb.Append(@"

             public override bool Equals(object obj)
         {
             if (obj is " + strTableName + @")
             {
                 " + strTableName + @" newObj = (" + strTableName + @")obj;

                 if ("+ strValue +@")
                 {
                     return true;
                 }
                 else
                 {
                     return false;
                 }
             }

             else
             {
                 return false;
             }

            
         }            











             ");
            
            
            
            
            sb.AppendLine("}");
            sb.AppendLine("}");



           




            return sb.ToString();
            
            
        }



        /// <summary>
        /// will generate table class empty that have properties of all table fields
        /// </summary>
        /// <param name="tableName">the table name</param>
        /// <param name="Connection">the connection to the database</param>
        /// <param name="Provider">the provider used to access the database</param>
        public static string GenerateTableClassEmpty(string tableName, string Connection, string Provider)
        {
            ClassDataHandler con = new ClassDataHandler(Connection, Provider);
            con.OpenConnection();

            DataTable dt = con.SelectSchemaInDataTable(" select * from dbo.[" + tableName + "] where 1 <> 1 ", CommandType.Text);

            if (dt == null)
                throw new Exception("لا يوجد جدول بهذا الاسم");

            if (dt.Columns.Count == 0)
                throw new Exception("لا يوجد اعمدة في هذا الجدول");

            dt.TableName = tableName;


            con.CloseConnection();


            if (dt.Columns.Count == 0)
                return "";



            return GenerateClassEmpty(dt);



        }

        /// <summary>
        /// Generate table class empty from a datatable
        /// </summary>
        /// <param name="dtNew">the datatable to generate its class</param>
        private static string GenerateClassEmpty(DataTable dtNew)
        {
           
            StringBuilder sb = new StringBuilder("");
            string strTableName = dtNew.TableName.Replace(" ", "_");

            sb.AppendLine(@"

            


            ");

            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Web;");

            sb.AppendLine("namespace " + dbComponentGenerator.GlobalVars.strNameSpace + @"");
            sb.AppendLine("{");



            
            sb.AppendLine("     public partial class " + strTableName);
            sb.AppendLine("     {");


            sb.AppendLine(@"
            //Write any additional methods or properties here 
            //Don't modify any genrated classes");



            sb.AppendLine("     }");
            sb.AppendLine("}");


            return sb.ToString();


        }




        /// <summary>
        /// this function will get the initial value to each type
        /// </summary>
        /// <param name="currentType">the type</param>
        /// <returns>the value in a string</returns>
        public static string getTypeInitialValue(DataColumn dc)
        {
            if (dc.AllowDBNull)
                return "null";


            Type currentType = dc.DataType;


            if (currentType == Type.GetType("System.UInt64"))
                return "0";

            if (currentType == Type.GetType("System.Int64"))
                return "-1";

            if (currentType == Type.GetType("System.Array"))
                return "new byte[0]";

            if (currentType == Type.GetType("System.Boolean"))
                return "false";


            if (currentType == Type.GetType("System.Char"))
                return "''";

            if (currentType == Type.GetType("System.DateTime"))
                return "DateTime.Now";

            if (currentType == Type.GetType("System.Decimal"))
                return "-1";


            if (currentType == Type.GetType("System.Double"))
                return "-1";


            if (currentType == Type.GetType("System.UInt32"))
                return "0";

            if (currentType == Type.GetType("System.Int32"))
                return "-1";

            if (currentType == Type.GetType("System.String"))
                return "\"\"";

            if (currentType == Type.GetType("System.Single"))
                return "-1";



            if (currentType == Type.GetType("System.UInt16"))
                return "0";

            if (currentType == Type.GetType("System.Int16"))
                return "-1";


            if (currentType == Type.GetType("System.Byte"))
                return "0";


            if (currentType == Type.GetType("System.Object"))
                return "new object()";

            

            return "null";
        }


        /// <summary>
        /// this will return the string for defining an object from the column type
        /// </summary>
        /// <param name="dc">the data column</param>
        /// <returns>the string of the type</returns>
        public static string getTypeDeclarationFromColumn (DataColumn dc)
        {
            byte[] btCheck = new byte[0];

            if (dc.DataType == btCheck.GetType())
                return "byte[]";

            if (dc.AllowDBNull)
            {

                if (dc.DataType == Type.GetType("System.UInt64"))
                    return "System.UInt64?";

                if (dc.DataType == Type.GetType("System.Int64"))
                    return "System.Int64?";



                if (dc.DataType == Type.GetType("System.Boolean"))
                    return "System.Boolean?";


                if (dc.DataType == Type.GetType("System.Char"))
                    return "System.Char?";

                if (dc.DataType == Type.GetType("System.DateTime"))
                    return "System.DateTime?";

                if (dc.DataType == Type.GetType("System.Decimal"))
                    return "System.Decimal?";


                if (dc.DataType == Type.GetType("System.Double"))
                    return "System.Double?";


                if (dc.DataType == Type.GetType("System.UInt32"))
                    return "System.UInt32?";

                if (dc.DataType == Type.GetType("System.Int32"))
                    return "System.Int32?";

                if (dc.DataType == Type.GetType("System.String"))
                    return "System.String";

                if (dc.DataType == Type.GetType("System.Single"))
                    return "System.Single?";



                if (dc.DataType == Type.GetType("System.UInt16"))
                    return "System.UInt16?";

                if (dc.DataType == Type.GetType("System.Int16"))
                    return "System.Int16?";


                if (dc.DataType == Type.GetType("System.Byte"))
                    return "System.Byte?";


                if (dc.DataType == Type.GetType("System.Object"))
                    return "System.Object";
            }
            else
            {
                return dc.DataType.FullName;
            }



            return "System.Object";
        }




        /// <summary>
        /// this function will get the DbType value to each type
        /// </summary>
        /// <param name="dc">the column to get its dbType</param>
        /// <returns>the value in a string</returns>
        public static string getDbType(DataColumn dc)
        {

            byte[] btCheck = new byte[0];


            Type currentType = dc.DataType;

            if (currentType == btCheck.GetType())
                return "DbType.Binary";



            if (currentType == Type.GetType("System.UInt64"))
                return "DbType.UInt64";

            if (currentType == Type.GetType("System.Int64"))
                return "DbType.Int64";

            if (currentType == Type.GetType("System.Array"))
                return "DbType.Binary";

            


            if (currentType == Type.GetType("System.Boolean"))
                return "DbType.Boolean";


            if (currentType == Type.GetType("System.Char"))
                return "DbType.String";

            if (currentType == Type.GetType("System.DateTime"))
                return "DbType.DateTime";

            if (currentType == Type.GetType("System.Decimal"))
                return "DbType.Decimal";


            if (currentType == Type.GetType("System.Double"))
                return "DbType.Double";


            if (currentType == Type.GetType("System.UInt32"))
                return "DbType.UInt32";

            if (currentType == Type.GetType("System.Int32"))
                return "DbType.Int32";

            if (currentType == Type.GetType("System.String"))
                return "DbType.String";

            if (currentType == Type.GetType("System.Single"))
                return "DbType.Single";



            if (currentType == Type.GetType("System.UInt16"))
                return "DbType.UInt16";

            if (currentType == Type.GetType("System.Int16"))
                return "DbType.Int16";


            if (currentType == Type.GetType("System.Byte"))
                return "DbType.Byte";


            if (currentType == Type.GetType("System.Object"))
                return "DbType.Object";



            return "DbType.Object";
        }


    }
}
